import os
import sys
import openpyxl as openpyxl
from openpyxl import Workbook
from openpyxl.styles import Border, Side

PATH = os.path.dirname(os.path.dirname(__file__))
test_path = os.path.dirname(__file__)


"封装openxy库"


class ExcelHandle:
    """初始化excel数据"""

    def __init__(self, filepath):
        self.filepath = filepath
        self.open_excel = openpyxl.load_workbook(filename=self.filepath)

    # 根据下标获取excel列表sheet数据
    def getSheetsData(self, index):
        try:
            sheet_data = self.open_excel.worksheets[index - 1]

        except IndexError:

            print('当前excel共有{0}个sheet，输入的下标{1}'.format(len(self.getSheetsList), index - 1))
            sys.exit()
        return sheet_data

    '创建sheet页'

    def add_sheet_name(self, sheet_name):
        sheet_list = self.getSheetsList
        if sheet_name in sheet_list:
            sys.exit('sheet名称已存在，当前excel，sheet名称%s' % (sheet_list))
        '获取当前excel表sheet总长，在最后插入新sheet'
        len_sheet = len(self.getSheetsList) - 1
        self.open_excel.create_sheet(sheet_name, len_sheet + 1)
        self.saveExcel(self.filepath)

    '创建新excel表格'

    @staticmethod
    def addExcel(filename):
        '创建工作表对象'
        create_work = Workbook()
        create_work.create_sheet('result')
        curr_path = os.getcwd()
        file_name = curr_path + '\\' + filename
        # if os.path.exists(file_name):
        #     return '文件名称重复'
        # else:
        create_work.save(file_name)

    '获取表格sheet信息,返回list'

    @property
    def getSheetsList(self):
        return self.open_excel.sheetnames

    '读取excel表头数据--按列表输出。示例：[[1,2,3],[4,5,6]]'

    def getExcelDataList(self, index):
        row_line = self.getSheetsData(index).rows
        excel_data_list = []
        for row in row_line:
            excel_list = []
            for cell in row:
                cell_values = str(cell.value)
                excel_list.append(cell_values)
            excel_data_list.append(excel_list)

        return excel_data_list

    '读取excel数据--按字典数据，示例:{[a:1,b:2,c:3],[a:4]}'

    def getExcelDataDict(self, index):
        excel_data = self.getExcelDataList(index)
        first_title = excel_data[0]
        data_list = []
        for i in range(1, len(excel_data)):
            data_dict = {}
            for j in range(len(excel_data[i])):
                data_dict[first_title[j]] = excel_data[i][j]
            data_list.append(data_dict)
        return data_list

    '获取指定行数据'

    def getRowData(self, row, index):
        excel_row = self.getExcelCol(index=index)
        if row > excel_row:
            sys.exit('不存在行，当前excel总行数为：{0},输入列数：{1}'.format(excel_row, row))
        row_list = []
        for i in range(excel_row):
            row_list.append(self.getSheetsData(index).cell(row=row, column=i + 1).value)
        return row_list

    '获取指定列数据'

    def getColData(self, col, index):
        excel_col = self.getExcelCol(index=index)
        if col > excel_col:
            sys.exit('不存在列，当前excel总列数为：{0},输入列数：{1}'.format(excel_col, col))
        else:
            col_list = []
            for i in range(excel_col):
                col_list.append(self.getSheetsData(index).cell(row=i + 1, column=col).value)
            return col_list

    '获取excel表格总行数'

    def getExcelRow(self, index):
        total_row = self.getSheetsData(index).max_row
        return total_row

    '获取excel表格纵列数'

    def getExcelCol(self, index):
        total_cell = self.getSheetsData(index).max_column
        return total_cell

    '根据行与列读取excel数据'

    def getIndexData(self, row, col, index):
        try:
            cell_value = self.getSheetsData(index).cell(row=row, column=col).value
        except ValueError as e:
            return ("数据行与列值错误：{0}").format(e)
        except TypeError as e:
            return ("数据行与列类型错误：{0}").format(e)
        return cell_value

    '保存excel文件'

    def saveExcel(self, file_path):
        self.open_excel.save(file_path)

    '根据行与列写入数据'

    def writeIndexData(self, index, data=None):
        for i in range(len(data)):
            for j in range(len(data[i])):
                self.getSheetsData(index=index).cell(row=i + 1, column=j + 1).value = data[i][j]
        self.saveExcel(self.filepath)

    '比较单个excel表格两行数据是否相同，相同返回True，不同返回False'

    def compareRowData(self, l_col, r_col, index):
        self.getSheetsData(index=index)
        left_data = self.getColData(col=l_col, index=index)
        right_data = self.getColData(col=r_col, index=index)
        len_l = len(left_data)
        temp = 0
        res_list = []
        while temp < len_l:
            if left_data[temp] == right_data[temp] and left_data[temp] != None:
                res_list.append(True)
                temp += 1
            else:
                res_list.append(False)
                temp += 1
        return res_list

    # 处理合并单元格的行数据
    def mergeRowData(self, index, merge_width):
        # 获取当前excel最大行和列，校验是否越界
        row_len = self.getExcelRow(index=index)
        row_merge_list = []
        # 根据合并宽度分解数据成二元列表
        if merge_width == 1:
            for i in range(1, row_len + 1, merge_width + 1):
                list = []
                for j in range(1):
                    list.append(i)
                    list.append(i + merge_width)
                row_merge_list.append(list)
        else:
            for i in range(1, row_len + 1, merge_width):
                list = []
                for j in range(1):
                    list.append(i)
                    list.append(i + merge_width - 1)
                row_merge_list.append(list)
        return row_merge_list

    # 过滤列表中的空列表，返回新列表
    @staticmethod
    def filterData(data):
        filer_list = []
        for i in range(len(data)):
            if data[i] != []:
                filer_list.append(data[i])
        return filer_list

    # 处理合并单元格列数据
    def mergeColData(self, index, col_l, col_r, merge_width):
        # 获取当前excel最大行和列，校验是否越界
        col_len = self.getExcelCol(index=index)
        col_merge_list = []
        # 列表推导式获取26个英文字母
        col_title = [chr(i) for i in range(65, 91)]
        col_l_index = col_title.index(col_l)
        col_r_index = col_title.index(col_r)
        merge_col = col_title[col_l_index:col_r_index + 1]
        res = len(merge_col) // merge_width
        if col_len >= len(merge_col) and len(merge_col) >= merge_width:
            # 根据合并宽度分解数据成二元列表
            if merge_width == 1:
                inde_l = 0
                index_r = merge_width + 1
                for i in range(res):
                    col_merge_list.append(merge_col[inde_l:index_r])
                    inde_l += 2
                    index_r += 2
            else:
                inde_l = 0
                index_r = merge_width
                for i in range(res):
                    col_merge_list.append(merge_col[inde_l:index_r])
                    inde_l += 2
                    index_r += 2

            return self.filterData(col_merge_list)
        else:
            return '当前列超过excel表格最大列，当前表格总列数为：%d,传入数据列：%d,分割列表的列为：%d' % (
                col_len, len(merge_col), merge_width)

    # 合并单元格
    def merge_cell(self, index, col_l=None, col_r=None, flag=1, merge_width=None):
        temp = 0
        # 根据flag判断是行合并还是列合并，1：行。2：列
        if flag == 1:
            # 根据行合并单元格
            mergeRow = self.mergeRowData(index=index, merge_width=merge_width)
            while temp < len(mergeRow):
                self.getSheetsData(index=index).merge_cells(
                    '{0}{1}:{0}{2}'.format(col_l, mergeRow[temp][0], mergeRow[temp][1]))
                temp += 1
        # 根据列合并单元格
        else:
            col_data = self.mergeColData(index=index, col_l=col_l, col_r=col_r, merge_width=merge_width)
            if isinstance(col_data, list):
                while temp < len(col_data):
                    # 列默认只对首行进行合并
                    self.getSheetsData(index=index).merge_cells(
                        '{0}{2}:{1}{2}'.format(col_data[temp][0], col_data[temp][1], 1))
                    print(col_data)
                    print(col_data[temp][0], col_data[temp][1])
                    temp += 1
            else:
                raise IndexError(col_data)
        self.saveExcel(self.filepath)

    # 增加边框线
    def border(self, index):
        col_data = self.getExcelCol(index)
        row_data = self.getExcelRow(index)
        thin_broder = Border(left=Side(style='thin'),
                             right=Side(style='thin'),
                             top=Side(style='thin'),
                             bottom=Side(style='thin'))
        for i in range(1, col_data + 1):
            for j in range(1, row_data + 1):
                self.getSheetsData(index).cell(j, i).border = thin_broder
        self.saveExcel(self.filepath)


if __name__ == '__main__':
    oo = ExcelHandle('./12.xlsx')
    print(oo.getSheetsList)

    print(oo.getSheetsData(0))

    print(oo.getExcelDataList(0))
    # ww = oo.merge_cell(index=0,col_l='A',col_r='D',merge_width=1,flag=2)
    # zz = oo.mergeColData(index=0,col_l="A",col_r='D',merge_width=1)
    # oo.writeIndexData(index=0)
    # print(ww)
